/* 
 *   Date       Developer   Description
 *   
 *
 *   Classname:  com.ytincl.scdp.common.dbinfo.impl.TableInfoOracleImpl
 * 
 *   Date:   Nov 30, 2007
 * 
 *   The source code contained in this listing is proprietary to
 *   HLJ POST YITONG INFO-NET CO.,LTD.
 * 
 *   Unauthorized copying, adaptation, distribution, use, or display
 *   is strictly prohibited.
 *   This software is Copyright 2007 HLJ POST YITONG INFO-NET CO.,LTD.
 */
package com.titan.core.utils.dbinfo.impl;

import java.sql.Connection;

import com.titan.core.utils.db.DBUtils;
import com.titan.core.utils.db.RecordSet;
import com.titan.core.utils.dbinfo.interfaces.TableInfo;


/**
 * @author 周成功
 * @email 86898837@qq.com
 * @date 2020年02月20日
 */
public class TableInfoOracleImpl implements TableInfo {
    // {列英文名, 数据类型, 数据长度, 数值指定长度(整数+小数), 数值小数部分长度, 是否可以为null(Y/N), 列注释}
    public String[][] getColumnInfo(Connection conn, String tablename) throws Exception {
        return getColumnInfo(conn, null, tablename);
    }
    public String[][] getColumnInfo(Connection conn, String schema, String tablename) throws Exception {
        StringBuffer sql = new StringBuffer(1024);
        sql.append("select user_tab_columns.column_name");
        sql.append("       ,user_tab_columns.data_type");
        sql.append("       ,user_tab_columns.data_length");
        sql.append("       ,user_tab_columns.data_precision");
        sql.append("       ,user_tab_columns.data_scale");
        sql.append("       ,user_tab_columns.nullable");
        sql.append("       ,user_col_comments.comments");
        sql.append("       from user_tab_columns,user_col_comments");
        sql.append("       where upper(user_tab_columns.table_name)=upper(?)");
        sql.append("       and user_tab_columns.table_name=user_col_comments.table_name");
        sql.append("       and user_tab_columns.column_name=user_col_comments.column_name");
        sql.append("       order by user_tab_columns.column_id");
        RecordSet rest = DBUtils.executeQueryToRecordSet(conn, sql.toString(), new String[]{tablename});
        String[][] result =new String[rest.getRowCount()][7];
        for (int row = 0; row < rest.getRowCount(); row++) {
            result[row][0] =rest.getString(row + 1, "column_name");
            result[row][1] =rest.getString(row + 1, "data_type");
            result[row][2] =Integer.toString(rest.getInt(row + 1, "data_length"));
            result[row][3] =Integer.toString(rest.getInt(row + 1, "data_precision"));
            result[row][4] =Integer.toString(rest.getInt(row + 1, "data_scale"));
            result[row][5] =rest.getString(row + 1, "nullable");
            result[row][6] =rest.getString(row + 1, "comments");
        }
        return result;
    }
    
    
    public String[] getPrimaryKey(Connection conn, String tablename) throws Exception {
        return getPrimaryKey(conn, null, tablename);
    }
    public String[] getPrimaryKey(Connection conn, String schema, String tablename) throws Exception {
        StringBuffer sql = new StringBuffer(1024);
        sql.append("select column_name");
        sql.append("       from user_cons_columns,user_constraints");
        sql.append("       where user_cons_columns.constraint_name=user_constraints.constraint_name");
        sql.append("       and user_cons_columns.owner=user_constraints.owner");
        sql.append("       and user_cons_columns.table_name=user_constraints.table_name");
        sql.append("       and user_constraints.constraint_type='P'");
        sql.append("       and upper(user_cons_columns.table_name)=upper(?)");
        sql.append("       order by user_cons_columns.position");
        RecordSet rest = DBUtils.executeQueryToRecordSet(conn, sql.toString(), new String[]{tablename});
        String[] primaryKey = new String[rest.getRowCount()];
        for (int i = 0; i < rest.getRowCount(); i++) {
            primaryKey[i] = rest.getString(i + 1, "column_name");
        }
        return primaryKey;
    }
    
    
    // 表类型, 表注释
    public String[] getTableInfo(Connection conn, String tablename) throws Exception {
        return getTableInfo(conn, null, tablename);
    }
    public String[] getTableInfo(Connection conn, String schema, String tablename) throws Exception {
        // 获得表信息
        StringBuffer sql = new StringBuffer(1024);
        sql.append("select table_type, comments");
        sql.append("       from user_tab_comments");
        sql.append("       where upper(table_name)=upper(?)");
        RecordSet rest = DBUtils.executeQueryToRecordSet(conn, sql.toString(), new String[]{tablename});
        if (rest.moveNext()) {
            return new String[]{rest.getString("table_type"), rest.getString("comments")};
        } else {
            return null;
        }
    }
    @Override
    public String[][] getUniqueIndex(Connection conn, String tablename) throws Exception {
        // TODO Auto-generated method stub
        return null;
    }
    @Override
    public String[][] getUniqueIndex(Connection conn, String schema, String tablename) throws Exception {
        // TODO Auto-generated method stub
        return null;
    }

}
